package com.demo.main.utils;

import java.lang.reflect.Field;
import java.sql.Date;
import java.sql.*;
import java.util.*;

public class JDBCUtil {
    private static final int MYSQL_VERSION = 8;
    private static final String USERNAME = "root";
    private static final String PASSWORD = "zwz050418";
    private static final String DATABASE_NAME = "lms_javaweb_b30";

    private static final String DRIVER_CLASS_NAME;
    private static final String URL;
    private static final boolean AUTO_COMMIT = true;
    private static final int PAGE_SIZE = PaginationUtil.DEFAULT_PAGE_SIZE;
    private static final boolean printSQLEnabled = false;

    private static Connection conn;
    private static PreparedStatement preparedStatement;
    private static ResultSet resultSet;

    static {
        Map<Integer, Map<String, String>> mysqlVersionMap = new HashMap<>(2);

        mysqlVersionMap.put(5, new HashMap<String, String>() {{
            put("driver", "com.mysql.jdbc.Driver");
            put("url", "jdbc:mysql://localhost:3306/" + DATABASE_NAME + "?useUnicode=true&characterEncoding=utf8&useSSL=false");
        }});
        mysqlVersionMap.put(8, new HashMap<String, String>() {{
            put("driver", "com.mysql.cj.jdbc.Driver");
            // &useSSL=true
            put("url", "jdbc:mysql://localhost:3306/" + DATABASE_NAME + "?characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&tinyInt1isBit=true&rewriteBatchedStatements=true");
        }});

        DRIVER_CLASS_NAME = mysqlVersionMap.get(MYSQL_VERSION).get("driver");
        URL = mysqlVersionMap.get(MYSQL_VERSION).get("url");

        try {
            Class.forName(DRIVER_CLASS_NAME);
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            conn.setAutoCommit(AUTO_COMMIT);
        } catch (ClassNotFoundException | SQLException e) {
            System.err.println("初始化数据库连接失败: " + e.getMessage());
            e.printStackTrace();
        }
    }

    private final String tableName;
    private final String primaryKeyColumn;

    public JDBCUtil(String tableName, String primaryKeyColumn) {
        this.tableName = tableName;
        this.primaryKeyColumn = primaryKeyColumn;
    }

    private static <T> T executeSQL(String sql, SQLExecutor<T> executor, Object... params) {
        try {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            preparedStatement = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                preparedStatement.setObject(i + 1, typeof(params[i]));
            }

            if (printSQLEnabled) {
                System.out.println("Executing SQL: " + sql);
                System.out.println("Parameters: " + String.join(", ", Arrays.toString(params)));
            }

            return executor.execute(preparedStatement);
        } catch (SQLException e) {
            System.err.println("执行SQL失败: " + e.getMessage());
            e.printStackTrace();
            rollback(conn);
            return null;
        }
    }

    private static void handleConditions(Map<String, Object> additionalConditions, StringBuilder SQL, List<Object> params, List<String> conditions) {
        if (additionalConditions != null) {
            for (Map.Entry<String, Object> entry : additionalConditions.entrySet()) {
                String column = entry.getKey();
                Object value = entry.getValue();

                if (isValidValue(value)) {
                    if (value instanceof Map) {
                        @SuppressWarnings("unchecked")
                        Map<Object, Operator> conditionMap = (Map<Object, Operator>) value;
                        Map.Entry<Object, Operator> conditionEntry = conditionMap.entrySet().iterator().next();
                        Object conditionValue = conditionEntry.getKey();
                        Operator operator = conditionEntry.getValue();

                        switch (operator) {
                            case LIKE:
                                conditions.add(column + " LIKE ?");
                                params.add("%" + conditionValue + "%");
                                break;
                            case LIKE_START:
                                conditions.add(column + " LIKE ?");
                                params.add(conditionValue + "%");
                                break;
                            case LIKE_END:
                                conditions.add(column + " LIKE ?");
                                params.add("%" + conditionValue);
                                break;
                            case EQUALS:
                            default:
                                conditions.add(column + " = ?");
                                params.add(conditionValue);
                                break;
                        }
                    } else {
                        conditions.add(column + " = ?");
                        params.add(value);
                    }
                }
            }
        }

        if (!conditions.isEmpty()) {
            SQL.append(" WHERE ");
            SQL.append(String.join(" AND ", conditions));
        }
    }

    public static ResultSet query(String SQL, Object... params) {
        if (SQL == null || SQL.trim().isEmpty() || !SQL.trim().toLowerCase().startsWith("select")) {
            throw new IllegalArgumentException("SQL语句为空或非查询语句");
        }

        return executeSQL(SQL, ps -> {
            if (resultSet != null) {
                resultSet.close();
            }
            resultSet = ps.executeQuery();
            return resultSet;
        }, params);
    }

    public static boolean execute(String SQL, Object... params) {
        if (SQL == null || SQL.trim().isEmpty() || SQL.trim().toLowerCase().startsWith("select")) {
            throw new IllegalArgumentException("SQL语句为空或有错");
        }
        Boolean result = executeSQL(SQL, ps -> {
            ps.executeUpdate();
            commit(conn);
            return true;
        }, params);
        return result != null && result;
    }

    public static int insert(String SQL, boolean autoGeneratedKeys, Object... params) {
        if (SQL == null || SQL.trim().isEmpty() || !SQL.trim().toLowerCase().startsWith("insert")) {
            throw new IllegalArgumentException("SQL语句为空或非插入语句");
        }
        Integer result = executeSQL(SQL, ps -> {
            int updateCount = ps.executeUpdate();
            if (autoGeneratedKeys && updateCount > 0) {
                try (ResultSet rs = ps.getGeneratedKeys()) {
                    if (rs.next()) {
                        return rs.getInt(1);
                    }
                }
            }
            return autoGeneratedKeys ? 0 : updateCount;
        }, params);
        return result != null ? result : -1;
    }

    private static Object typeof(Object o) {
        if (o instanceof Timestamp) {
            return o;
        }
        if (o instanceof java.util.Date) {
            return new Date(((java.util.Date) o).getTime());
        }
        if (o instanceof Character) {
            return String.valueOf(o);
        }
        return o;
    }

    private static void commit(Connection c) {
        if (c != null) {
            try {
                if (!c.getAutoCommit()) {
                    c.commit();
                }
            } catch (SQLException e) {
                System.err.println("提交事务失败: " + e.getMessage());
                e.printStackTrace();
            }
        }
    }

    private static void rollback(Connection c) {
        if (c != null) {
            try {
                if (!c.getAutoCommit()) {
                    c.rollback();
                }
            } catch (SQLException e) {
                System.err.println("回滚事务失败: " + e.getMessage());
                e.printStackTrace();
            }
        }
    }

    public static String camelToSnake(String camelCaseStr) {
        StringBuilder snakeCaseStr = new StringBuilder();
        for (int i = 0; i < camelCaseStr.length(); i++) {
            char c = camelCaseStr.charAt(i);
            if (Character.isUpperCase(c)) {
                if (i > 0) {
                    snakeCaseStr.append('_');
                }
                snakeCaseStr.append(Character.toLowerCase(c));
            } else {
                snakeCaseStr.append(c);
            }
        }
        return snakeCaseStr.toString();
    }

    // remove backticks
    public static String removeBackticks(String input) {
        if (input == null || input.length() < 2) {
            return input;
        }

        if (input.startsWith("`") && input.endsWith("`")) {
            return input.substring(1, input.length() - 1);
        }

        return input;
    }

    public static ResultSet executeConditionalQueryWithOrder(String baseSql, Map<String, Object> conditions, Map<String, Boolean> orderBy) {
        if (baseSql == null || baseSql.trim().isEmpty()) {
            throw new IllegalArgumentException("基础SQL语句不能为空");
        }

        StringBuilder sqlBuilder = new StringBuilder(baseSql);
        List<Object> params = new ArrayList<>();
        boolean whereClauseAdded = baseSql.toLowerCase().contains("where");

        if (conditions != null && !conditions.isEmpty()) {
            for (Map.Entry<String, Object> entry : conditions.entrySet()) {
                String column = entry.getKey();
                Object value = entry.getValue();

                if (isValidValue(value)) {
                    if (value instanceof Map) {
                        @SuppressWarnings("unchecked")
                        Map<Object, Operator> conditionMap = (Map<Object, Operator>) value;
                        Map.Entry<Object, Operator> conditionEntry = conditionMap.entrySet().iterator().next();
                        Object conditionValue = conditionEntry.getKey();
                        Operator operator = conditionEntry.getValue();

                        if (operator == Operator.LIKE && (conditionValue == null || conditionValue.toString().isEmpty())) {
                            continue;
                        }

                        if (!whereClauseAdded) {
                            sqlBuilder.append(" WHERE ");
                            whereClauseAdded = true;
                        } else {
                            sqlBuilder.append(" AND ");
                        }

                        switch (operator) {
                            case LIKE:
                                sqlBuilder.append(column).append(" LIKE ?");
                                params.add("%" + conditionValue + "%");
                                break;
                            case LIKE_START:
                                sqlBuilder.append(column).append(" LIKE ?");
                                params.add(conditionValue + "%");
                                break;
                            case LIKE_END:
                                sqlBuilder.append(column).append(" LIKE ?");
                                params.add("%" + conditionValue);
                                break;
                            case EQUALS:
                            default:
                                sqlBuilder.append(column).append(" = ?");
                                params.add(conditionValue);
                                break;
                        }
                    } else {
                        if (!whereClauseAdded) {
                            sqlBuilder.append(" WHERE ");
                            whereClauseAdded = true;
                        } else {
                            sqlBuilder.append(" AND ");
                        }
                        sqlBuilder.append(column).append(" = ?");
                        params.add(value);
                    }
                }
            }
        }

        if (orderBy != null && !orderBy.isEmpty()) {
            sqlBuilder.append(" ORDER BY ");
            List<String> orderClauses = new ArrayList<>();
            for (Map.Entry<String, Boolean> entry : orderBy.entrySet()) {
                orderClauses.add(entry.getKey() + (entry.getValue() ? " ASC" : " DESC"));
            }
            sqlBuilder.append(String.join(", ", orderClauses));
        }

        String finalSql = sqlBuilder.toString();

        return query(finalSql, params.toArray());
    }

    private static boolean isValidValue(Object value) {
        if (value == null) {
            return false;
        }
        if (value instanceof String) {
            return !((String) value).trim().isEmpty();
        }
        if (value instanceof Map) {
            @SuppressWarnings("unchecked")
            Map<Object, Operator> map = (Map<Object, Operator>) value;
            if (map.isEmpty()) {
                return false;
            }
            Object key = map.keySet().iterator().next();
            return key != null && !(key instanceof String && ((String) key).trim().isEmpty());
        }
        return true;
    }

    // 分页的executeConditionalQueryWithOrder方法
    public static ResultSet executeConditionalQueryWithOrderAndPagination(String baseSql, Map<String, Object> conditions, Map<String, Boolean> orderBy, int page, int pageSize) {
        if (baseSql == null || baseSql.trim().isEmpty()) {
            throw new IllegalArgumentException("基础SQL语句不能为空");
        }

        StringBuilder sqlBuilder = new StringBuilder(baseSql);
        List<Object> params = new ArrayList<>();
        boolean whereClauseAdded = baseSql.toLowerCase().contains("where");

        if (conditions != null && !conditions.isEmpty()) {
            for (Map.Entry<String, Object> entry : conditions.entrySet()) {
                String column = entry.getKey();
                Object value = entry.getValue();

                if (isValidValue(value)) {
                    if (value instanceof Map) {
                        @SuppressWarnings("unchecked")
                        Map<Object, Operator> conditionMap = (Map<Object, Operator>) value;
                        Map.Entry<Object, Operator> conditionEntry = conditionMap.entrySet().iterator().next();
                        Object conditionValue = conditionEntry.getKey();
                        Operator operator = conditionEntry.getValue();

                        if (operator == Operator.LIKE && (conditionValue == null || conditionValue.toString().isEmpty())) {
                            continue;
                        }

                        if (!whereClauseAdded) {
                            sqlBuilder.append(" WHERE ");
                            whereClauseAdded = true;
                        } else {
                            sqlBuilder.append(" AND ");
                        }

                        switch (operator) {
                            case LIKE:
                                sqlBuilder.append(column).append(" LIKE ?");
                                params.add("%" + conditionValue + "%");
                                break;
                            case LIKE_START:
                                sqlBuilder.append(column).append(" LIKE ?");
                                params.add(conditionValue + "%");
                                break;
                            case LIKE_END:
                                sqlBuilder.append(column).append(" LIKE ?");
                                params.add("%" + conditionValue);
                                break;
                            case EQUALS:
                            default:
                                sqlBuilder.append(column).append(" = ?");
                                params.add(conditionValue);
                                break;
                        }
                    } else {
                        if (!whereClauseAdded) {
                            sqlBuilder.append(" WHERE ");
                            whereClauseAdded = true;
                        } else {
                            sqlBuilder.append(" AND ");
                        }
                        sqlBuilder.append(column).append(" = ?");
                        params.add(value);
                    }
                }
            }
        }

        if (orderBy != null && !orderBy.isEmpty()) {
            sqlBuilder.append(" ORDER BY ");
            List<String> orderClauses = new ArrayList<>();
            for (Map.Entry<String, Boolean> entry : orderBy.entrySet()) {
                orderClauses.add(entry.getKey() + (entry.getValue() ? " ASC" : " DESC"));
            }
            sqlBuilder.append(String.join(", ", orderClauses));
        }

        // 添加分页
        sqlBuilder.append(" LIMIT ?, ?");
        params.add((page - 1) * pageSize);
        params.add(pageSize);

        String finalSql = sqlBuilder.toString();

        return query(finalSql, params.toArray());
    }

    // 批量删除方法（静态版本）
    public static boolean batchDelete(String tableName, String columnName, List<Object> ids) {
        if (ids == null || ids.isEmpty()) {
            return false;
        }
        StringBuilder SQL = new StringBuilder("DELETE FROM " + tableName + " WHERE " + columnName + " IN (");
        for (int i = 0; i < ids.size(); i++) {
            SQL.append("?,");
        }
        SQL.setLength(SQL.length() - 1);
        SQL.append(")");
        return execute(SQL.toString(), ids.toArray());
    }

    public static int selectConditionalQueryCount(String baseSql, Map<String, Object> additionalConditions) {
        if (baseSql == null || baseSql.trim().isEmpty()) {
            throw new IllegalArgumentException("基础SQL语句不能为空");
        }

        StringBuilder sqlBuilder = new StringBuilder("SELECT COUNT(*) FROM (");
        sqlBuilder.append(baseSql);
        sqlBuilder.append(") AS subquery");

        List<Object> params = new ArrayList<>();
        List<String> whereConditions = new ArrayList<>();

        if (additionalConditions != null && !additionalConditions.isEmpty()) {
            for (Map.Entry<String, Object> entry : additionalConditions.entrySet()) {
                String column = entry.getKey();
                Object value = entry.getValue();

                if (isEffectiveConditionValue(value)) {
                    if (value instanceof Map) {
                        @SuppressWarnings("unchecked")
                        Map<Object, Operator> conditionMap = (Map<Object, Operator>) value;
                        if (!conditionMap.isEmpty()) {
                            Map.Entry<Object, Operator> conditionEntry = conditionMap.entrySet().iterator().next();
                            Object conditionValue = conditionEntry.getKey();
                            Operator operator = conditionEntry.getValue();

                            if (isEffectiveConditionValue(conditionValue)) {
                                switch (operator) {
                                    case LIKE:
                                        whereConditions.add(column + " LIKE ?");
                                        params.add("%" + conditionValue + "%");
                                        break;
                                    case LIKE_START:
                                        whereConditions.add(column + " LIKE ?");
                                        params.add(conditionValue + "%");
                                        break;
                                    case LIKE_END:
                                        whereConditions.add(column + " LIKE ?");
                                        params.add("%" + conditionValue);
                                        break;
                                    case EQUALS:
                                    default:
                                        whereConditions.add(column + " = ?");
                                        params.add(conditionValue);
                                        break;
                                }
                            }
                        }
                    } else {
                        whereConditions.add(column + " = ?");
                        params.add(value);
                    }
                }
            }
        }

        if (!whereConditions.isEmpty()) {
            sqlBuilder.append(" WHERE ");
            sqlBuilder.append(String.join(" AND ", whereConditions));
        }

        String finalSql = sqlBuilder.toString();

        ResultSet rs = query(finalSql, params.toArray());
        try {
            if (rs != null && rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            System.err.println("执行count查询失败: " + e.getMessage());
            e.printStackTrace();
        }
        return -1; // 表示查询失败
    }

    // 辅助方法：检查值是否有效
    private static boolean isEffectiveConditionValue(Object value) {
        if (value == null) {
            return false;
        }
        if (value instanceof String) {
            return !((String) value).trim().isEmpty();
        }
        if (value instanceof Map) {
            return !((Map<?, ?>) value).isEmpty();
        }
        return true;
    }

    /**
     * 调用存储过程并返回结果。
     *
     * @param procedureName 存储过程的名称。
     * @param params        传递给存储过程的参数。
     * @return StoredProcedureResult 包含执行结果的对象。
     * @throws SQLException 如果数据库访问错误或其他错误发生时抛出。
     */
    public static StoredProcedureResult callStoredProcedure(String procedureName, Object... params) throws SQLException {
        // 构建调用存储过程的SQL字符串
        String sql = buildCallString(procedureName, params.length);
        // 使用try-with-resources语句确保资源被正确关闭
        try (CallableStatement cstmt = conn.prepareCall(sql)) {
            // 设置存储过程的输入参数
            setParameters(cstmt, params);
            // 注册输出参数，第一个是布尔类型，第二个是字符串类型
            cstmt.registerOutParameter(params.length + 1, Types.BOOLEAN);
            cstmt.registerOutParameter(params.length + 2, Types.VARCHAR);
            // 执行存储过程，检查是否有结果集返回
            boolean hasResultSet = cstmt.execute();
            // 创建用于存储结果的对象
            StoredProcedureResult result = new StoredProcedureResult();
            // 从输出参数中获取执行成功标志和消息
            result.setSuccess(cstmt.getBoolean(params.length + 1));
            result.setMessage(cstmt.getString(params.length + 2));
            // 如果存在结果集，处理结果集
            if (hasResultSet) {
                try (ResultSet rs = cstmt.getResultSet()) {
                    // 将结果集映射到列表中
                    result.setResultList(ResultSetUtil.mapToList(rs, Object.class));
                }
            }
            // 返回封装了所有结果的对象
            return result;
        }
    }

    /**
     * 构建用于调用存储过程的字符串。
     *
     * @param procedureName 存储过程的名称。
     * @param paramCount    输入参数的数量。
     * @return 返回一个表示存储过程调用的字符串，格式为 "{CALL procedureName(?,?,...)}", 其中问号代表参数占位符。
     */
    private static String buildCallString(String procedureName, int paramCount) {
        // 使用StringBuilder来构建字符串，首先添加"{CALL "和存储过程名称
        StringBuilder sb = new StringBuilder("{CALL ").append(procedureName).append("(");
        // 循环添加问号作为参数占位符，每个参数之间用逗号分隔
        for (int i = 0; i < paramCount; i++) {
            // 如果是第一个参数，前面不加逗号，否则在每个参数前加逗号
            sb.append(i == 0 ? "?" : ",?");
        }
        // 在参数列表后添加两个输出参数占位符
        sb.append(",?,?)}");
        // 返回构建好的字符串
        return sb.toString();
    }

    /**
     * 设置CallableStatement的参数。
     *
     * @param cstmt  CallableStatement对象，用于执行存储过程。
     * @param params 参数数组，包含所有需要传递给存储过程的参数。
     * @throws SQLException 如果数据库访问错误或其他错误发生时抛出。
     */
    private static void setParameters(CallableStatement cstmt, Object[] params) throws SQLException {
        // 遍历参数数组，将每个参数设置到CallableStatement中
        for (int i = 0; i < params.length; i++) {
            // 注意：SQL中的参数索引是从1开始的，因此使用i + 1
            cstmt.setObject(i + 1, params[i]);
        }
    }

    private ResultSet selectSelectivePaginationInternal(Object entity, Map<String, Object> additionalConditions, int page, int pageSize) {
        if (entity == null) {
            throw new IllegalArgumentException("实体对象不能为空");
        }
        if (page <= 0 || pageSize <= 0) {
            throw new IllegalArgumentException("页码和每页大小必须大于0");
        }

        StringBuilder SQL = new StringBuilder("SELECT * FROM ").append(tableName);
        List<Object> params = new ArrayList<>();
        List<String> conditions = new ArrayList<>();

        // 处理实体对象的条件
        Field[] fields = entity.getClass().getDeclaredFields();
        extractEntityConditions(entity, params, conditions, fields);

        // 处理额外的条件
        handleConditions(additionalConditions, SQL, params, conditions);

        SQL.append(" LIMIT ?, ?");
        params.add((page - 1) * pageSize);
        params.add(pageSize);

        return query(SQL.toString(), params.toArray());
    }

    private StringBuilder buildSelectSQL(Object entity, boolean isSelective) {
        StringBuilder SQL = new StringBuilder("SELECT * FROM ").append(tableName);
        Field[] fields = entity.getClass().getDeclaredFields();
        List<String> conditions = new ArrayList<>();

        for (Field field : fields) {
            if (field.getName().equals("serialVersionUID")) continue;
            field.setAccessible(true);
            try {
                Object value = field.get(entity);
                if (value != null || !isSelective) {
                    conditions.add(camelToSnake(field.getName()) + " = ?");
                }
            } catch (IllegalAccessException e) {
                System.err.println("访问字段失败: " + e.getMessage());
                e.printStackTrace();
            }
        }

        if (!conditions.isEmpty()) {
            SQL.append(" WHERE ");
            SQL.append(String.join(" AND ", conditions));
        }

        return SQL;
    }

    private void extractEntityConditions(Object entity, List<Object> params, List<String> conditions, Field[] fields) {
        for (Field field : fields) {
            if (field.getName().equals("serialVersionUID")) continue;
            field.setAccessible(true);
            try {
                Object value = field.get(entity);
                if (value != null) {
                    conditions.add(camelToSnake(field.getName()) + " = ?");
                    params.add(value);
                }
            } catch (IllegalAccessException e) {
                System.err.println("访问字段失败: " + e.getMessage());
                e.printStackTrace();
            }
        }
    }

    private List<Object> getEntityFieldValues(Object entity, boolean includeNullValues) {
        List<Object> params = new ArrayList<>();
        Field[] fields = entity.getClass().getDeclaredFields();

        for (Field field : fields) {
            if (field.getName().equals("serialVersionUID")) continue;
            field.setAccessible(true);
            try {
                Object value = field.get(entity);
                if (value != null || includeNullValues) {
                    params.add(value);
                }
            } catch (IllegalAccessException e) {
                System.err.println("访问字段失败: " + e.getMessage());
                e.printStackTrace();
            }
        }
        return params;
    }

    private ResultSet selectSelectivePaginationInternal(Object entity, Map<String, Object> additionalConditions, int page, int pageSize, Map<String, Boolean> orderBy) {
        if (page <= 0 || pageSize <= 0) {
            throw new IllegalArgumentException("页码和每页大小必须大于0");
        }

        StringBuilder SQL = new StringBuilder("SELECT * FROM ").append(tableName);
        List<Object> params = new ArrayList<>();
        List<String> conditions = new ArrayList<>();

        // 处理实体对象的条件（如果实体对象不为空）
        if (entity != null) {
            Field[] fields = entity.getClass().getDeclaredFields();
            extractEntityConditions(entity, params, conditions, fields);
        }

        // 处理额外的条件
        handleConditions(additionalConditions, SQL, params, conditions);

        // 添加排序
        if (orderBy != null && !orderBy.isEmpty()) {
            SQL.append(" ORDER BY ");
            List<String> orderClauses = new ArrayList<>();
            for (Map.Entry<String, Boolean> entry : orderBy.entrySet()) {
                orderClauses.add(entry.getKey() + (entry.getValue() ? " ASC" : " DESC"));
            }
            SQL.append(String.join(", ", orderClauses));
        }

        SQL.append(" LIMIT ?, ?");
        params.add((page - 1) * pageSize);
        params.add(pageSize);

        return query(SQL.toString(), params.toArray());
    }

    public boolean deleteById(Object id) {
        String SQL = "DELETE FROM " + tableName + " WHERE " + primaryKeyColumn + " = ?";
        return execute(SQL, id);
    }

    public boolean updateSelective(Object entity) {
        StringBuilder SQL = new StringBuilder("UPDATE " + tableName + " SET ");
        List<Object> params = new ArrayList<>();

        Field[] fields = entity.getClass().getDeclaredFields();
        Field primaryKeyField = null;
        System.out.println(primaryKeyColumn);
        for (Field field : fields) {
            if (field.getName().equals("serialVersionUID")) continue;
            field.setAccessible(true);
            try {
                Object value = field.get(entity);
                if (value != null) {
                    System.out.println(field.getName());
                    if (camelToSnake(field.getName()).equals(removeBackticks(primaryKeyColumn))) {
                        primaryKeyField = field;
                    } else {
                        SQL.append(camelToSnake(field.getName())).append(" = ?,");
                        params.add(value);
                    }
                }
            } catch (IllegalAccessException e) {
                System.err.println("访问字段失败: " + e.getMessage());
                e.printStackTrace();
                return false;
            }
        }

        if (params.isEmpty()) {
            return false;
        }

        SQL.setLength(SQL.length() - 1);
        SQL.append(" WHERE ").append(primaryKeyColumn).append(" = ?");

        if (primaryKeyField == null) {
            System.err.println("未找到主键字段");
            return false;
        }

        try {
            params.add(primaryKeyField.get(entity));
        } catch (IllegalAccessException e) {
            System.err.println("访问主键字段失败: " + e.getMessage());
            e.printStackTrace();
            return false;
        }

        return execute(SQL.toString(), params.toArray());
    }

    public ResultSet selectAll() {
        String SQL = "SELECT * FROM " + tableName;
        return query(SQL);
    }

    public ResultSet selectById(Object id) {
        String SQL = "SELECT * FROM " + tableName + " WHERE " + primaryKeyColumn + " = ?";
        return query(SQL, id);
    }

    public ResultSet selectByColumn(String column, Object value) {
        if (column == null || column.trim().isEmpty()) {
            throw new IllegalArgumentException("列名不能为空");
        }
        String SQL = "SELECT * FROM " + tableName + " WHERE " + column + " = ?";
        return query(SQL, value);
    }

    public ResultSet selectByColumnDescById(String column, Object value) {
        if (column == null || column.trim().isEmpty()) {
            throw new IllegalArgumentException("列名不能为空");
        }
        String SQL = "SELECT * FROM " + tableName + " WHERE " + column + " = ? ORDER BY " + primaryKeyColumn + " DESC";
        return query(SQL, value);
    }

    public ResultSet selectSelective(Object entity) {
        StringBuilder SQL = buildSelectSQL(entity, true);
        List<Object> params = getEntityFieldValues(entity, false);

        if (params.isEmpty()) {
            return selectAll();
        }

        return query(SQL.toString(), params.toArray());
    }

    public ResultSet selectSelectiveDescById(Object entity) {
        StringBuilder SQL = buildSelectSQL(entity, true);
        List<Object> params = getEntityFieldValues(entity, false);

        if (params.isEmpty()) {
            return selectDescById();
        }

        SQL.append(" ORDER BY ").append(primaryKeyColumn).append(" DESC");

        return query(SQL.toString(), params.toArray());
    }

    public boolean deleteByIds(List<?> ids) {
        StringBuilder SQL = new StringBuilder("DELETE FROM " + tableName + " WHERE " + primaryKeyColumn + " IN (");
        for (int i = 0; i < ids.size(); i++) {
            SQL.append("?,");
        }
        SQL.deleteCharAt(SQL.length() - 1).append(")");
        return execute(SQL.toString(), ids.toArray());
    }

    public boolean insertSelective(Object entity) {
        StringBuilder SQL = new StringBuilder("INSERT INTO " + tableName + " (");
        StringBuilder values = new StringBuilder("VALUES (");
        List<Object> params = new ArrayList<>();

        Field[] fields = entity.getClass().getDeclaredFields();
        for (Field field : fields) {
            if ("serialVersionUID".equals(field.getName())) {
                continue;
            }
            field.setAccessible(true);
            try {
                Object value = field.get(entity);
                if (value != null) {
                    SQL.append(camelToSnake(field.getName())).append(",");
                    values.append("?,");
                    params.add(value);
                }
            } catch (IllegalAccessException e) {
                System.err.println("访问字段失败: " + e.getMessage());
                e.printStackTrace();
            }
        }

        if (params.isEmpty()) {
            return false;
        }

        SQL.setLength(SQL.length() - 1);
        values.setLength(values.length() - 1);
        SQL.append(") ").append(values).append(")");

        return insert(SQL.toString(), false, params.toArray()) > 0;
    }

    public boolean batchInsert(List<Object> entities) {
        if (entities == null || entities.isEmpty()) {
            return false;
        }

        Object firstEntity = entities.get(0);
        StringBuilder SQL = new StringBuilder("INSERT INTO " + tableName + " (");
        StringBuilder valueTemplate = new StringBuilder("(");
        List<String> columnNames = new ArrayList<>();

        Field[] fields = firstEntity.getClass().getDeclaredFields();
        for (Field field : fields) {
            if ("serialVersionUID".equals(field.getName())) {
                continue;
            }
            field.setAccessible(true);
            try {
                Object value = field.get(firstEntity);
                if (value != null) {
                    columnNames.add(field.getName());
                    SQL.append(camelToSnake(field.getName())).append(",");
                    valueTemplate.append("?,");
                }
            } catch (IllegalAccessException e) {
                System.err.println("访问字段失败: " + e.getMessage());
                return false;
            }
        }

        if (columnNames.isEmpty()) {
            return false;
        }

        SQL.setLength(SQL.length() - 1);
        valueTemplate.setLength(valueTemplate.length() - 1);
        valueTemplate.append(")");
        SQL.append(") VALUES ");

        List<Object[]> batchParams = new ArrayList<>();
        for (Object entity : entities) {
            List<Object> params = new ArrayList<>();
            for (String columnName : columnNames) {
                try {
                    Field field = entity.getClass().getDeclaredField(columnName);
                    field.setAccessible(true);
                    params.add(field.get(entity));
                } catch (Exception e) {
                    System.err.println("获取字段值失败: " + e.getMessage());
                    return false;
                }
            }
            batchParams.add(params.toArray());
        }

        String valuesClause = valueTemplate.toString();
        String finalSQL = SQL + String.join(",", Collections.nCopies(entities.size(), valuesClause));

        Object[] allParams = batchParams.stream()
                .flatMap(Arrays::stream)
                .toArray();

        return insert(finalSQL, false, allParams) > 0;
    }

    // 统计查询
    public long count() {
        String SQL = "SELECT COUNT(*) FROM " + tableName;
        ResultSet rs = query(SQL);
        try {
            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            System.err.println("执行count查询失败: " + e.getMessage());
            e.printStackTrace();
        }
        return -1; // 表示查询失败
    }

    public long selectCount() {
        return count();
    }

    public <T> long selectCountByCondition(T entity) {
        StringBuilder SQL = new StringBuilder("SELECT COUNT(*) FROM ").append(tableName);
        List<Object> params = new ArrayList<>();
        Field[] fields = entity.getClass().getDeclaredFields();
        List<String> conditions = new ArrayList<>();

        extractEntityConditions(entity, params, conditions, fields);

        if (!conditions.isEmpty()) {
            SQL.append(" WHERE ");
            SQL.append(String.join(" AND ", conditions));
        }

        ResultSet rs = query(SQL.toString(), params.toArray());
        try {
            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            System.err.println("执行count查询失败: " + e.getMessage());
            e.printStackTrace();
        }
        return -1; // 表示查询失败
    }

    // 按主键降序排列的方法
    public ResultSet selectDescById() {
        String SQL = "SELECT * FROM " + tableName + " ORDER BY " + primaryKeyColumn + " DESC";
        return query(SQL);
    }

    public ResultSet selectAllDescByEntity(Object entity) {
        StringBuilder SQL = new StringBuilder("SELECT * FROM ").append(tableName);
        List<Object> params = new ArrayList<>();
        Field[] fields = entity.getClass().getDeclaredFields();
        List<String> conditions = new ArrayList<>();

        extractEntityConditions(entity, params, conditions, fields);

        if (!conditions.isEmpty()) {
            SQL.append(" WHERE ");
            SQL.append(String.join(" AND ", conditions));
        }

        SQL.append(" ORDER BY ").append(primaryKeyColumn).append(" DESC");

        return query(SQL.toString(), params.toArray());
    }

    public ResultSet selectPaginationDesc(int page) {
        if (page <= 0) {
            throw new IllegalArgumentException("页码必须大于0");
        }
        String SQL = "SELECT * FROM " + tableName + " ORDER BY " + primaryKeyColumn + " DESC LIMIT ?, ?";
        int offset = (page - 1) * PAGE_SIZE;
        return query(SQL, offset, PAGE_SIZE);
    }

    public ResultSet selectSelectivePaginationDescById(Object entity, int page) {
        Map<String, Boolean> orderBy = new LinkedHashMap<>();
        orderBy.put(primaryKeyColumn, false);
        return selectSelectivePaginationInternal(entity, null, page, PAGE_SIZE, orderBy);
    }

    public enum Operator {
        EQUALS, LIKE, LIKE_START, LIKE_END
    }

    @FunctionalInterface
    private interface SQLExecutor<T> {
        T execute(PreparedStatement ps) throws SQLException;
    }

    public static class StoredProcedureResult {
        private boolean success;
        private String message;
        private List<?> resultList;

        public StoredProcedureResult() {
        }

        public StoredProcedureResult(boolean success, String message) {
            this.success = success;
            this.message = message;
        }

        public boolean isSuccess() {
            return success;
        }

        public void setSuccess(boolean success) {
            this.success = success;
        }

        public String getMessage() {
            return message;
        }

        public void setMessage(String message) {
            this.message = message;
        }

        public List<?> getResultList() {
            return resultList;
        }

        public void setResultList(List<?> resultList) {
            this.resultList = resultList;
        }
    }
}